﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Modellayer;
using System.Data;
using System.ServiceModel;

namespace Dblayer
{
    public class DbReservation
    {
        private SqlCommand cmd = null;
        private DbShow dbShow;
        private DbPerson dbPerson;
        private DbSeat dbSeat;

        public DbReservation()
        {
            dbShow = new DbShow();
            dbPerson = new DbPerson();
            dbSeat = new DbSeat();
        }


        public void createReservation(Reservation reservation)
        {
            try
            {
                Connection.dbconn.Open();
                string insert = @"insert into reservation(reservationid, showid, phonenumber) values ('" + reservation.reservationId + "','" + reservation.show.showId + "','" + reservation.person.phoneNumber + "')";
                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("Fejl: " + e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }

        public void deleteReservation(int reservationId)
        {
            try
            {
                Connection.dbconn.Open();
                string insert = "delete from reservation where reservationid = '" + reservationId + "'";
                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (FaultException fault)
            {
                Console.WriteLine("Error deleting movie, the reservationID does not exist\n\n " + fault.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Fejl: " + e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }

        public Reservation findReservation(int reservationId)
        {

            string sql = sql = "Select * from show show, Movie movie, Showroom showroom, Person person, Reservation reservation" +
                    " where person.phoneNumber = reservation.phoneNumber" +
                    " and show.showId = reservation.showId" +
                    " and movie.title = show.title" +
                    " and showroom.showroomNumber = show.showroomNumber" +
                    " and reservation.ReservationId = '" + reservationId + "'";


            return BuildReservation(sql);
        }

        private Reservation BuildReservation(string sql)
        {
            IDataReader dbReader;
            Reservation reservation = null;

            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                if (dbReader.Read())
                {
                    Movie movie = new Movie((dbReader["Title"].ToString()), (dbReader["movieLength"].ToString()));
                    ShowRoom showroom = new ShowRoom(int.Parse(dbReader["showRoomNumber"].ToString()), int.Parse(dbReader["numberOfSeats"].ToString()));
                    Show show = new Show(movie, showroom, int.Parse(dbReader["showId"].ToString()), dbReader["showTime"].ToString());
                    Person person = new Person(int.Parse(dbReader["phoneNumber"].ToString()), dbReader["Email"].ToString(), dbReader["password"].ToString(), dbReader["name"].ToString(), dbReader["loginId"].ToString(), dbReader["Persontype"].ToString());
                    reservation = new Reservation((int)dbReader["reservationid"], null, show, person);
                }
                Connection.Close();
            }
            catch (NullReferenceException ee)
            {
                Console.WriteLine("Could not find reservation \n\n" + ee.Message);
                reservation = null;
                Connection.Close();
            }
            catch (FaultException fault)
            {
                Console.WriteLine("Could not find reservation \n\n" + fault.Message);
                reservation = null;
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find reservation \n\n" + e.Message);
                reservation = null;
                Connection.Close();
            }
            return reservation;
        }

        public int getMax()
        {
            int getMax = 0;
            try
            {
                string sql = "select MAX(Reservationid) from ReservatIon";
                Connection.dbconn.Open();
                cmd = Connection.GetDBCommand(sql);
                getMax = (int)cmd.ExecuteScalar();
                getMax++;
            }

            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }

            return getMax;
        }

        public int getLastMax()
        {
            int getMax = 0;
            try
            {
                string sql = "select MAX(Reservationid) from ReservatIon";
                Connection.dbconn.Open();
                cmd = Connection.GetDBCommand(sql);
                getMax = (int)cmd.ExecuteScalar();
            }

            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }

            return getMax;
        }
    }
}

//public int getMax()
//{
//    int getMax = 0;
//    try
//    {
//        string sql = "select MAX(Reservationid) from ReservatIon";
//        Connection.dbconn.Open();
//        cmd = Connection.GetDBCommand(sql);
//        getMax = (int)cmd.ExecuteScalar();
//        Connection.Close();
//    }

//    catch (Exception e)
//    {
//        Console.WriteLine(e.Message);
//        Connection.Close();
//    }

//    return getMax;
//}


